﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OPTFleet.OPTFleet;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data;

namespace OPTFleet.Data
{
    class GestaoFuncionarios
    {
        private static String tabelaFuncionarios = "funcionarios";
        private static String[] chavesFuncionarios = { "idfunc" };
        private static String[] camposFuncionarios= { "cargo", "nome", "disponivel", "foto" };

        private static String tabelaMotoristasVeiculos = "motoristasveiculos";
        private static String[] chavesMotoristasVeiculos = { "idfunc" , "idtipo" };

        /*
        private static String tabelaTiposVeiculo = "tiposveiculo";
        private static String[] chavesTiposVeiculo = { "idtipo" };
        private static String[] camposTiposVeiculo = { "tipoveiculo" };
        */ 

        private Conexao sqlConnection;
        
        public Conexao SqlConnection
        {
            set { sqlConnection = value; }
        }

        public GestaoFuncionarios()
        {
            this.sqlConnection = new Conexao();
            this.sqlConnection.connect();
        }

        public void GetTiposMotorista(Funcionario f)
        {
            SqlConnection sqlCon;
            String stmtSql;
            Motorista m = (Motorista)f;
            String[] parameters = new String[10];
            SqlCommand sqlCmd;
            SqlDataReader rs;
            SortedSet<int> ss = new SortedSet<int>();

            sqlCon = (SqlConnection)sqlConnection.Connection;

            /*
            SELECT idtipo
            FROM motoristasveiculos
            WHERE motoristasveiculos.idfunc = idfunc
            */

            parameters[0] = chavesMotoristasVeiculos[1];
            parameters[1] = tabelaMotoristasVeiculos;
            parameters[3] = tabelaMotoristasVeiculos + "." + chavesMotoristasVeiculos[0];
            stmtSql = string.Format("SELECT {0} FROM {1} WHERE {3}=@IDFunc",parameters);

            sqlCmd = new SqlCommand(stmtSql, sqlCon);
            sqlCmd.Parameters.Add("@IDFunc", SqlDbType.Int);
            sqlCmd.Parameters["@IDFunc"].Value = f.IDfuncionario;

            rs = sqlCmd.ExecuteReader();
            while(rs.Read())
            {
                int i = (int)rs[chavesMotoristasVeiculos[1]];
                ss.Add(i);
            }

            m.TiposVeiculos = ss;

            rs.Close();
        }

        public Funcionario ConsultarFuncionario(int idfunc)
        {
            Funcionario f;
            SqlConnection sqlCon;
            SqlCommand sqlCmd;
            String[] parameters = new String[10];
            String stmtSql;
            SqlDataReader rs;

            sqlCon = (SqlConnection)sqlConnection.Connection;

            /*
             * SELECT idfunc,cargo,nome,disponivel,foto
             * FROM funcionarios
             * WHERE idfunc = idfunc
             */
            parameters[0] = tabelaFuncionarios + "." + chavesFuncionarios[0];
            parameters[1] = tabelaFuncionarios + "." + camposFuncionarios[0];
            parameters[2] = tabelaFuncionarios + "." + camposFuncionarios[1];
            parameters[3] = tabelaFuncionarios + "." + camposFuncionarios[2];
            parameters[4] = tabelaFuncionarios + "." + camposFuncionarios[3];
            parameters[5] = tabelaFuncionarios;
            parameters[6] = tabelaFuncionarios + "." + chavesFuncionarios[0];
            stmtSql = string.Format("SELECT {0},{1},{2},{3},{4} FROM {5} WHERE {6}=@IDFunc AND valido IS NULL", parameters);
            sqlCmd = new SqlCommand(stmtSql, sqlCon);

            sqlCmd.Parameters.Add("@IDFunc", SqlDbType.Int);
            sqlCmd.Parameters["@IDFunc"].Value = idfunc;

            rs = sqlCmd.ExecuteReader();

            f = MapFuncionario(rs);

            rs.Close();

            if (f == null) return f;

            switch (f.GetType().Name)
            {
                case "Motorista":
                    GetTiposMotorista(f);
                    break;
                default:
                    break;
            }
            return f;
        }

        private void AdicionarTiposVeiculo(Motorista m)
        {
            String stmtSql;
            SqlCommand sqlCmd;
            SqlConnection sqlCon;
            int idfunc;

            sqlCon = (SqlConnection)sqlConnection.Connection;

            stmtSql = "SELECT IDENT_CURRENT('funcionarios')";
            sqlCmd = new SqlCommand(stmtSql, sqlCon);

            idfunc = (int)(Decimal)sqlCmd.ExecuteScalar();

            foreach (int i in m.TiposVeiculos)
            {
                stmtSql = "INSERT INTO motoristasveiculos (idfunc,idtipo) VALUES(@IDFunc,@IDTipo)";
                sqlCmd = new SqlCommand(stmtSql, sqlCon);
                sqlCmd.Parameters.Add("@IDFunc", SqlDbType.Int);
                sqlCmd.Parameters["@IDFunc"].Value = idfunc;
                sqlCmd.Parameters.Add("@IDTipo", SqlDbType.Int);
                sqlCmd.Parameters["@IDTipo"].Value = i;

                sqlCmd.ExecuteNonQuery();
            }
        }

        public void AdicionarFuncionario(Funcionario f)
        {
            SqlConnection sqlCon;
            SqlCommand sqlCmd;
            String stmtSql;
            byte[] foto;

            sqlCon = (SqlConnection)sqlConnection.Connection;

            /* INSERT INTO funcionarios
             * (cargo,nome,disponivel,foto)
             * VALUES(...)
             */

            //sqlCon.BeginTransaction("addFunc");
            stmtSql = "INSERT INTO funcionarios (cargo,nome,disponivel,foto) VALUES(@Cargo,@Nome,@Disponivel,@Foto)";

            sqlCmd = new SqlCommand(stmtSql, sqlCon);
            sqlCmd.Parameters.Add("@Cargo", SqlDbType.NChar);
            sqlCmd.Parameters["@Cargo"].Value = f.GetType().Name;
            sqlCmd.Parameters.Add("@Nome", SqlDbType.NChar);
            sqlCmd.Parameters["@Nome"].Value = f.Nome;
            sqlCmd.Parameters.Add("@Disponivel", SqlDbType.Int);
            sqlCmd.Parameters["@Disponivel"].Value = (f.Disponivel == true) ? 1 : 0;
            sqlCmd.Parameters.Add("@Foto", SqlDbType.VarBinary);
            foto = f.Foto;
            sqlCmd.Parameters["@Foto"].Size = foto.Length;
            sqlCmd.Parameters["@Foto"].Value = foto;

            sqlCmd.ExecuteNonQuery();

            if (f.GetType().Name.Equals("Motorista"))
            {
                Motorista m = (Motorista)f;
                AdicionarTiposVeiculo(m);
            }
        }

        public void RemoverTiposVeiculo(Motorista m)
        {
            SqlConnection sqlCon;
            SqlCommand sqlCmd;
            String stmtSql;
            int idfunc;

            sqlCon = (SqlConnection)sqlConnection.Connection;

            idfunc = m.IDfuncionario;

            stmtSql = "UPDATE motoristasveiculos SET valido = @Data WHERE idfunc = @IDFunc";
            sqlCmd = new SqlCommand(stmtSql,sqlCon);
            sqlCmd.Parameters.Add("@Data", SqlDbType.SmallDateTime);
            sqlCmd.Parameters["@Data"].Value = DateTime.Now;
            sqlCmd.Parameters.Add("@IDFunc", SqlDbType.Int);
            sqlCmd.Parameters["@IDFunc"].Value = idfunc;

            sqlCmd.ExecuteNonQuery();
        }

        public void RemoverFuncionario(Funcionario f)
        {
            SqlConnection sqlCon;
            SqlCommand sqlCmd;
            String stmtSql;

            sqlCon = (SqlConnection)sqlConnection.Connection;

            if(f.GetType().Name.Equals("Motorista"))
                RemoverTiposVeiculo((Motorista) f);

            stmtSql = "UPDATE funcionarios SET valido = @Data WHERE idfunc = @IDFunc";
            sqlCmd = new SqlCommand(stmtSql, sqlCon);

            sqlCmd.Parameters.Add("@Data",SqlDbType.SmallDateTime);
            sqlCmd.Parameters["@Data"].Value = DateTime.Now;
            sqlCmd.Parameters.Add("@IDFunc", SqlDbType.Int);
            sqlCmd.Parameters["@IDFunc"].Value = f.IDfuncionario;

            sqlCmd.ExecuteNonQuery();
        }

        public void AlterarTiposVeiculo(Funcionario f)
        {
            SqlConnection sqlCon;
            SqlCommand sqlCmd;
            String stmtSql;
            Motorista m = (Motorista)f;

            sqlCon = (SqlConnection)sqlConnection.Connection;
            stmtSql = "DELETE FROM motoristasveiculos WHERE idfunc=@IDFunc";
            sqlCmd = new SqlCommand(stmtSql,sqlCon);

            sqlCmd.Parameters.Add("@IDFunc", SqlDbType.Int);
            sqlCmd.Parameters["@IDFunc"].Value = f.IDfuncionario;

            sqlCmd.ExecuteNonQuery();

            foreach (int i in m.TiposVeiculos)
            {
                stmtSql = "INSERT INTO motoristasveiculos (idfunc,idtipo) VALUES(@IDFunc,@IDTipo)";
                sqlCmd = new SqlCommand(stmtSql, sqlCon);
                sqlCmd.Parameters.Add("@IDFunc", SqlDbType.Int);
                sqlCmd.Parameters["@IDFunc"].Value = m.IDfuncionario;
                sqlCmd.Parameters.Add("@IDTipo", SqlDbType.Int);
                sqlCmd.Parameters["@IDTipo"].Value = i;

                sqlCmd.ExecuteNonQuery();
            }
        }

        public void AlterarFuncionario(Funcionario f)
        {
            SqlConnection sqlCon;
            SqlCommand sqlCmd;
            String stmtSql;
            byte[] foto;

            sqlCon = (SqlConnection)sqlConnection.Connection;
            stmtSql = "UPDATE funcionarios SET nome=@Nome,disponivel=@Disponivel,foto=@Foto WHERE idfunc=@IDFunc";
            sqlCmd = new SqlCommand(stmtSql, sqlCon);

            sqlCmd.Parameters.Add("@IDFunc", SqlDbType.Int);
            sqlCmd.Parameters["@IDFunc"].Value = f.IDfuncionario;
            sqlCmd.Parameters.Add("@Nome", SqlDbType.NChar);
            sqlCmd.Parameters["@Nome"].Value = f.Nome;
            sqlCmd.Parameters.Add("@Disponivel", SqlDbType.Int);
            sqlCmd.Parameters["@Disponivel"].Value = (f.Disponivel == true) ? 1 : 0;
            sqlCmd.Parameters.Add("@Foto", SqlDbType.VarBinary);
            foto = f.Foto;
            sqlCmd.Parameters["@Foto"].Size = foto.Length;
            sqlCmd.Parameters["@Foto"].Value = foto;

            if (f.GetType().Name.Equals("Motorista"))
            {
                AlterarTiposVeiculo(f);
            }

            sqlCmd.ExecuteNonQuery();
        }
        private Funcionario MapFuncionario(DbDataReader dr)
        {
            SqlDataReader sdr = (SqlDataReader)dr;
            Funcionario f = null;
            int idfunc;
            String cargo, nome;
            int disponivel;
            byte[] imagem;

            if (!sdr.Read()) return f;

            idfunc = (int)sdr[chavesFuncionarios[0]];
            nome = ((String)sdr[camposFuncionarios[1]]).Trim();
            disponivel = (int)sdr[camposFuncionarios[2]];
            cargo = ((String)sdr[camposFuncionarios[0]]).Trim();
            imagem = (byte[])sdr[camposFuncionarios[3]];

            cargo = cargo.Trim();
            switch (cargo)
            {
                case "Motorista":
                    f = new Motorista(idfunc, nome, (disponivel == 1) ? true : false, imagem, new SortedSet<int>());
                    break;
                case "Gestor":
                    f = new Gestor(idfunc, nome, (disponivel == 1) ? true : false, imagem);
                    break;
                case "Admin":
                    f = new Admin(idfunc, nome, (disponivel == 1) ? true : false, imagem);
                    break;
                default:
                    break;
            }
            return f;
        }
        public List<Motorista> GetMotoristasDisponiveis()
        {
            List<Motorista> lst = new List<Motorista>();
            SqlCommand sqlCmd;
            SqlConnection sqlCon;
            String stmtSql,nome;
            SqlDataReader rs;
            Motorista m;
            int idfunc;

            sqlCon = (SqlConnection)sqlConnection.Connection;

            /* SELECT DISTINCT idfunc 
            * FROM funcionarios
            * WHERE funcionarios.valido IS NULL
            * AND funcionarios.cargo = @Motorista
            * AND funcionarios.disponivel = 1
            * //AND funcionarios.idfunc NOT IN (SELECT planeamentos.idfunc FROM planeamentos WHERE planeamentos.data = @Dia)
            * ORDER BY idfunc
            */

            stmtSql = "SELECT idfunc,nome FROM funcionarios WHERE funcionarios.valido IS NULL AND funcionarios.cargo = @Motorista AND funcionarios.disponivel = 1 ORDER BY idfunc";

            sqlCmd = new SqlCommand(stmtSql, sqlCon);

            sqlCmd.Parameters.Add("@Motorista",SqlDbType.NChar);
            sqlCmd.Parameters["@Motorista"].Value = "Motorista";

            rs = sqlCmd.ExecuteReader();

            while(rs.Read())
            {
                idfunc = (int)rs["idfunc"];
                nome = ((string)rs["nome"]).Trim();
                m = new Motorista(idfunc,nome, true, new byte[1], new SortedSet<int>());
                lst.Add(m);
            }

            rs.Close();

            foreach(Motorista mt in lst)
                GetTiposMotorista(mt);

            return lst;
        }

        public SortedDictionary<Int32, String> GetListFuncionarios()
        {
            SortedDictionary<Int32, String> sd = new SortedDictionary<Int32, String>();
            SqlConnection sqlCon;
            SqlCommand sqlCmd;
            String stmtSql;
            SqlDataReader rs;
            Int32 idf;
            String nome;

            sqlCon = (SqlConnection)sqlConnection.Connection;

            stmtSql = string.Format("SELECT idfunc,nome FROM funcionarios WHERE valido IS NULL ORDER BY idfunc");
            sqlCmd = new SqlCommand(stmtSql, sqlCon);

            rs = sqlCmd.ExecuteReader();

            while (rs.Read())
            {
                idf = (Int32)rs["idfunc"];
                nome = ((String)rs["nome"]).Trim();
                sd.Add(idf, nome);
            }

            rs.Close();

            return sd;
        }
    }
}
